﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LabTurtorial
{
    public partial class Lab06 : Form
    {
        SqlConnection sqlConProductDetails;
        SqlCommand sqlComandProductDetails;
        SqlDataReader sqlReaderProductDetails;
        int rowNumber;
        string operation;
        string record;
        string connectionString;
        public Lab06()
        {
            InitializeComponent();
            connectionString = "Data Source = localhost\\sqlexpress;database = Lab06_AutoMobile;Integrated Security = true";
            sqlConProductDetails = new SqlConnection(connectionString);
            sqlComandProductDetails = new SqlCommand();
        }

        private void Lab06_Load(object sender, EventArgs e)
        {
            try
            {
                if (sqlConProductDetails.State == ConnectionState.Open)
                {
                    sqlConProductDetails.Close();
                }
                sqlConProductDetails.Open();
                sqlComandProductDetails.CommandText = "SELECT *FROM Lab06_AutoMobile.dbo.ProductDetail";
                sqlComandProductDetails.Connection = sqlConProductDetails;
                sqlReaderProductDetails = sqlComandProductDetails.ExecuteReader();
                if (sqlReaderProductDetails.HasRows)
                {
                    sqlReaderProductDetails.Read();
                    DisplayResults();
                    if (sqlReaderProductDetails.Read())
                    {
                        btnNext.Enabled = true;
                        btnLast.Enabled = true;
                        rowNumber = rowNumber + 1;
                        btnPrevious_Click(null, null);
                    }
                    else
                    {
                        btnFirst.Enabled = false;
                        btnPrevious.Enabled = false;
                        btnNext.Enabled = false;
                        btnLast.Enabled = false;
                    }
                    btnEdit.Enabled = true;
                    btnDelete.Enabled = true;
                }
                else
                {
                    DisableButtons();
                    btnCancel.Enabled = false;
                    btnSave.Enabled = false;
                    btnAdd.Enabled = true;
                }
            }
            catch (SqlException ex) {
                MessageBox.Show(ex.Message);
                sqlConProductDetails.Close();
            }
        }

        public void DisplayResults() {
            txtModel.Text = sqlReaderProductDetails.GetString(0);
            txtManufacturer.Text = sqlReaderProductDetails.GetString(1);
            txtBuiltYear.Text = sqlReaderProductDetails.GetInt32(2).ToString();
            txtVIN.Text = sqlReaderProductDetails.GetString(3);
            txtCylinders.Text = sqlReaderProductDetails.GetInt32(4).ToString();
            txtColor.Text = sqlReaderProductDetails.GetString(5);
            txtEngineNumber.Text = sqlReaderProductDetails.GetInt32(6).ToString();
            txtPrice.Text = sqlReaderProductDetails.GetInt32(7).ToString();

        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (sqlReaderProductDetails.Read())
            {
                rowNumber = rowNumber + 1;
                DisplayResults();
            }
            else {
                MessageBox.Show("This is the last record","Message Box",MessageBoxButtons.OK, MessageBoxIcon.Information);
                btnNext.Enabled = false;
                btnLast.Enabled = false;
            }
            btnPrevious.Enabled = true;
            btnFirst.Enabled = true;
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            while (sqlReaderProductDetails.Read())
            {
                rowNumber = rowNumber + 1;
                DisplayResults();
            }
            btnFirst.Enabled = true;
            btnPrevious.Enabled = true;
            btnNext.Enabled = false;
            btnLast.Enabled = false;
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            int currentRow = 0;
            if (!sqlReaderProductDetails.IsClosed)
                sqlReaderProductDetails.Close();
            sqlReaderProductDetails = sqlComandProductDetails.ExecuteReader();
            if (rowNumber != 0)
            {
                while (currentRow < rowNumber)
                {
                    currentRow = currentRow + 1;
                    sqlReaderProductDetails.Read();
                }
                DisplayResults();
                rowNumber = rowNumber - 1;
            }
            else {
                MessageBox.Show("This is the first record", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Information);
                sqlReaderProductDetails.Read();
                btnFirst.Enabled = false;
                btnPrevious.Enabled = false;
            }
            btnNext.Enabled = true;
            btnLast.Enabled = true;
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (!sqlReaderProductDetails.IsClosed)
                sqlReaderProductDetails.Close();
            sqlReaderProductDetails = sqlComandProductDetails.ExecuteReader();
            if (rowNumber != 0)
            {
                sqlReaderProductDetails.Read();
                DisplayResults();
                rowNumber = 0;
            }
            else {
                MessageBox.Show("This is the first record","Message Box",MessageBoxButtons.OK,MessageBoxIcon.Information);
                sqlReaderProductDetails.Read();
            }
            btnFirst.Enabled = false;
            btnPrevious.Enabled = false;
            btnNext.Enabled = true;
            btnLast.Enabled = true;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            ClearControl();
            operation = "addnew";
            DisableButtons();
            btnSave.Enabled = true;
            btnCancel.Enabled = true;
            txtModel.Focus();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            string addNewString = "";
            /* Validation Form in here
                code validate in here
            */
            if (operation == "addnew")
            {
                addNewString = "INSERT INTO ProductDetail VALUES ('" + txtModel.Text + "','" + txtManufacturer.Text + "'," + txtBuiltYear.Text + ",'" + txtVIN.Text + "'," + txtCylinders.Text + ",'" + txtColor.Text + "'," + txtEngineNumber.Text + "," + txtPrice.Text + ")";
            }
            else if(operation == "edit"){
                addNewString = "UPDATE ProductDetail SET Manufacturer = ' " + txtManufacturer.Text + "', BuiltYear = " + txtBuiltYear.Text + ", VIN = '" + txtVIN.Text + "', Cylinders = " + txtCylinders.Text + ", Color = '" + txtColor.Text + "', EngineNumber = " + txtEngineNumber.Text + ", Price = " + txtPrice.Text + " WHERE Model = '" + txtModel.Text + "'";
            }

            if (operation != "") {
                if (!sqlReaderProductDetails.IsClosed)
                    sqlReaderProductDetails.Close();
                sqlComandProductDetails.CommandText = addNewString;
                sqlComandProductDetails.Connection = sqlConProductDetails;
                record = "Model: " + txtModel.Text + "\nManufacturer: " + txtManufacturer.Text + "\nBuilt Year: " + txtBuiltYear.Text + "\nVIN: " + txtVIN.Text + "\nCylinders: " + txtCylinders.Text + "\nColor: " + txtColor.Text + "\nEngine Number: " + txtEngineNumber.Text + "\nPrice: " + txtPrice.Text;
              
                //Cau lenh su dung cau querry addNewString
                sqlComandProductDetails.ExecuteNonQuery();
                MessageBox.Show("The following details have been saveed successfully.\n"+ record,"Message Box", MessageBoxButtons.OK,MessageBoxIcon.Information);
                Lab06_Load(null,null);
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            DisableButtons();
            operation = "edit";
            btnAdd.Enabled = false;
            btnSave.Enabled = true;
            btnCancel.Enabled = true;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            operation = "";
            Lab06_Load(null,null);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            record = "Model: " + txtModel.Text + "\nManufacturer: " + txtManufacturer.Text + "\nBuilt Year: " + txtBuiltYear.Text + "\nVIN: " + txtVIN.Text + "\nCylinders: " + txtCylinders.Text + "\nColor: " + txtColor.Text + "\nEngine Number: " + txtEngineNumber.Text + "\nPrice: " + txtPrice.Text;
            if (!sqlReaderProductDetails.IsClosed)
                sqlReaderProductDetails.Close();
            sqlComandProductDetails.CommandText = "DELETE FROM ProductDetail WHERE Model = '" +txtModel.Text+"'" ;
            if (sqlConProductDetails.State == ConnectionState.Open) {
                sqlConProductDetails.Close();
                sqlConProductDetails.Open();
                sqlComandProductDetails.ExecuteNonQuery();
                sqlConProductDetails.Close();
                MessageBox.Show("The Record has been deleted successfully.\n" + record,"Message Box",MessageBoxButtons.OK,MessageBoxIcon.Information);
                Lab06_Load(null,null);
            }
        }

        void ClearControl() {
            txtBuiltYear.Text = "";
            txtColor.Text = "";
            txtCylinders.Text = "";
            txtEngineNumber.Text = "";
            txtManufacturer.Text = "";
            txtModel.Text = "";
            txtPrice.Text = "";
            txtVIN.Text = "";
        }
        void DisableButtons() {
            btnEdit.Enabled = false;
            btnDelete.Enabled = false;
            btnFirst.Enabled = false;
            btnPrevious.Enabled = false;
            btnNext.Enabled = false;
            btnLast.Enabled = false;
            btnCancel.Enabled = false;
            btnAdd.Enabled = false;

        }

    }
}
